******SET YOUR DIRECTORY to ...\replication


foreach ss in "AL" "AZ" "CO" "CT" "DC" "DE" "FL" "GA" "IL" "IA" "IN" "KS" "KY" "LA" "MA" "MD" "ME" "MI" "MN" "MO" "MS" "NC" "NH" "NJ" "NM" "NY" "OH" "OK" "PA" "RI" "TN" "VT" "WA" "WI" "WV"{
	forval yy = 2012/2017 {
		clear all
		local fname = "rawdata\CDCcounty\" + "`ss'" + "_CountyLevelSummary_" + "`yy'" + ".xlsx"
		capture confirm file "`fname'"
		if _rc == 0 {
			import excel "`fname'", sheet("Sheet1")

			rename A FCOUNTY
			rename B county_name
			rename D num_tested
			rename E perc_tested
			rename F num_positive5
			rename G perc_positive5
			rename H num_positive10
			rename I perc_positive10

			keep FCOUNTY  county_name  num_tested perc_tested num_positive5 perc_positive5 num_positive10 perc_positive10

			destring FCOUNTY, replace force
			drop if FCOUNTY == .
			destring num_tested perc_tested num_positive5 perc_positive5 num_positive10 perc_positive10, replace force

			g STATE = "`ss'"
			g year = `yy'
			local fname = "rawdata\CDCcounty\" + "`ss'" + "_" + "`yy'" + ".dta"
			save "`fname'", replace
		}
	}
}



clear
local ii = 0
foreach ss in "AL" "AZ" "CO" "CT" "DC" "DE" "FL" "GA" "IL" "IA" "IN" "KS" "KY" "LA" "MA" "MD" "ME" "MI" "MN" "MO" "MS" "NC" "NH" "NJ" "NM" "NY" "OH" "OK" "PA" "RI" "TN" "VT" "WA" "WI" "WV"{
	forval yy = 2012/2017 {
		local fname = "rawdata\CDCcounty\" + "`ss'" + "_" + "`yy'" + ".dta"
		capture confirm file "`fname'"
		if _rc == 0 {
			local ii = `ii' + 1
			if `ii' ==  1 {
				use "`fname'"
			}
			else {
				append using "`fname'"
			}
		}
	}
}
save "rawdata\CDCcounty\_temp.dta", replace





use "finaldata\bridge_final.dta", replace

drop _merge
drop if city_center!= 1
duplicates drop STATE FMSA, force

drop if FCOUNTY==.
***county in mupliple FMSA
drop if STATE=="CT" & FCOUNTY==1
drop if STATE=="CT" & FCOUNTY==9
drop if STATE=="MA" & FCOUNTY==27
drop if STATE=="NH" & FCOUNTY==11


keep FMSA STATE FCOUNTY

save "rawdata\CDCcounty\bridge for merge.dta", replace


merge 1:n STATE FCOUNTY using "rawdata\CDCcounty\_temp.dta"

drop if _merge!=3
drop _merge

save "rawdata\CDCcounty\lead_blood.dta", replace



